from flask import Flask, request, jsonify, render_template
import pymysql

app = Flask(__name__, template_folder='')

# 数据库连接配置
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '111111',
    'db':'zhusunan',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

def get_db_connection():
    return pymysql.connect(**db_config)

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/submit', methods=['POST'])
def submit():
    data = request.get_json()
    student_id = data.get('student_id')
    name = data.get('name')
    course_name = data.get('course_name')
    grade = data.get('grade')

    if not all([student_id, name, course_name, grade]):
        return jsonify({'error': 'Missing required fields'}), 400

    connection = None  # 初始化connection变量
    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            sql = "INSERT INTO students (student_id, name, course_name, grade) VALUES (%s, %s, %s, %s)"
            cursor.execute(sql, (student_id, name, course_name, grade))
        connection.commit()
    except pymysql.Error as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if connection:  # 检查connection是否已定义
            connection.close()

    return jsonify({'message': 'Data submitted successfully'}), 201

@app.route('/records', methods=['GET'])
def records():
    connection = None
    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            sql = "SELECT * FROM students"
            cursor.execute(sql)
            result = cursor.fetchall()
        return jsonify(result), 200
    except pymysql.Error as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if connection:
            connection.close()

if __name__ == '__main__':
    app.run(host='0.0.0.0',debug=True)